/*
 Navicat Premium Data Transfer

 Source Server         : 10.108.8.17
 Source Server Type    : Oracle
 Source Server Version : 110200
 Source Host           : 10.108.8.17:1521
 Source Schema         : KETTLE_SCHEDULER

 Target Server Type    : Oracle
 Target Server Version : 110200
 File Encoding         : 65001

 Date: 17/04/2020 11:05:16
*/


-- ----------------------------
-- Table structure for K_CATEGORY
-- ----------------------------
DROP TABLE K_CATEGORY;
CREATE TABLE K_CATEGORY
(
    ID            NUMBER(11) NOT NULL,
    CATEGORY_NAME NVARCHAR2(50),
    ADD_TIME      DATE,
    ADD_USER      NUMBER(11),
    EDIT_TIME     DATE,
    EDIT_USER     NUMBER(11),
    DEL_FLAG      NUMBER(11)
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_CATEGORY.ID IS ''分类ID'';
COMMENT
ON COLUMN K_CATEGORY.CATEGORY_NAME IS ''分类名称'';
COMMENT
ON COLUMN K_CATEGORY.ADD_TIME IS ''添加时间'';
COMMENT
ON COLUMN K_CATEGORY.ADD_USER IS ''添加者'';
COMMENT
ON COLUMN K_CATEGORY.EDIT_TIME IS ''编辑时间'';
COMMENT
ON COLUMN K_CATEGORY.EDIT_USER IS ''编辑者'';
COMMENT
ON COLUMN K_CATEGORY.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT
ON TABLE K_CATEGORY IS ''分类表'';

-- ----------------------------
-- Records of K_CATEGORY
-- ----------------------------
INSERT INTO K_CATEGORY
VALUES ('' - 16 '', ''支付'', TO_DATE(''2019-12-30 23:49:15'', ''SYYYY-MM-DD HH24:MI:SS''), NULL,
        TO_DATE(''2019-12-30 23:49:15'', ''SYYYY-MM-DD HH24:MI:SS''), NULL, ''0'');

-- ----------------------------
-- Table structure for K_DATABASE_TYPE
-- ----------------------------
DROP TABLE K_DATABASE_TYPE;
CREATE TABLE K_DATABASE_TYPE
(
    ID          NUMBER(11) NOT NULL,
    CODE        NVARCHAR2(255),
    DESCRIPTION NVARCHAR2(255),
    ADD_TIME    DATE,
    ADD_USER    NUMBER(11),
    EDIT_TIME   DATE,
    EDIT_USER   NUMBER(11),
    DEL_FLAG    NUMBER(11)
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_DATABASE_TYPE.ID IS ''主键'';
COMMENT
ON COLUMN K_DATABASE_TYPE.CODE IS ''编码'';
COMMENT
ON COLUMN K_DATABASE_TYPE.DESCRIPTION IS ''描述'';
COMMENT
ON COLUMN K_DATABASE_TYPE.ADD_TIME IS ''添加时间'';
COMMENT
ON COLUMN K_DATABASE_TYPE.ADD_USER IS ''添加者'';
COMMENT
ON COLUMN K_DATABASE_TYPE.EDIT_TIME IS ''编辑时间'';
COMMENT
ON COLUMN K_DATABASE_TYPE.EDIT_USER IS ''编辑者'';
COMMENT
ON COLUMN K_DATABASE_TYPE.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT
ON TABLE K_DATABASE_TYPE IS ''数据库连接类型表'';

-- ----------------------------
-- Records of K_DATABASE_TYPE
-- ----------------------------
INSERT INTO K_DATABASE_TYPE
VALUES (''1'', ''DERBY'', ''Apache Derby'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''2'', ''AS/400'', ''AS/400'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''3'', ''INTERBASE'', ''Borland Interbase'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''4'', ''INFINIDB'', ''Calpont InfiniDB'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''5'', ''IMPALASIMBA'', ''Cloudera Impala'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''6'', ''DBASE'', ''dBase III, IV or 5 '', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''7'', ''EXASOL4'', ''Exasol 4'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''8'', ''EXTENDB'', ''ExtenDB'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''9'', ''FIREBIRD'', ''Firebird SQL'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''10'', ''GENERIC'', ''Generic database'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''11'', ''GOOGLEBIGQUERY'', ''Google BigQuery'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''12'', ''GREENPLUM'', ''Greenplum'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''13'', ''SQLBASE'', ''Gupta SQL Base'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''14'', ''H2'', ''H2'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''15'', ''HIVE'', ''Hadoop Hive'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''16'', ''HIVE2'', ''Hadoop Hive 2/3'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''17'', ''HYPERSONIC'', ''Hypersonic'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''18'', ''DB2'', ''IBM DB2'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''19'', ''IMPALA'', ''Impala'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''20'', ''INFOBRIGHT'', ''Infobright'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''21'', ''INFORMIX'', ''Informix'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''22'', ''INGRES'', ''Ingres'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''23'', ''VECTORWISE'', ''Ingres VectorWise'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''24'', ''CACHE'', ''Intersystems Cache'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''25'', ''KINGBASEES'', ''KingbaseES'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''26'', ''LucidDB'', ''LucidDB'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''27'', ''MARIADB'', ''MariaDB'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''28'', ''SAPDB'', ''MaxDB (SAP DB)'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''29'', ''MONETDB'', ''MonetDB'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''30'', ''MSACCESS'', ''MS Access'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''31'', ''MSSQL'', ''MS SQL Server'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''32'', ''MSSQLNATIVE'', ''MS SQL Server (Native)'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''33'', ''MYSQL'', ''MySQL'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''34'', ''MONDRIAN'', ''Native Mondrian'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''35'', ''NEOVIEW'', ''Neoview'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''36'', ''NETEZZA'', ''Netezza'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''37'', ''ORACLE'', ''Oracle'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''38'', ''ORACLERDB'', ''Oracle RDB'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''39'', ''PALO'', ''Palo MOLAP Server'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''40'', ''KettleThin'', ''Pentaho Data Services'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''41'', ''POSTGRESQL'', ''PostgreSQL'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''42'', ''REDSHIFT'', ''Redshift'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''43'', ''REMEDY-AR-SYSTEM'', ''Remedy Action Request System'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''44'', ''SAPR3'', ''SAP ERP System'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''45'', ''SNOWFLAKEHV'', ''Snowflake'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''46'', ''SPARKSIMBA'', ''SparkSQL'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''47'', ''SQLITE'', ''SQLite'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''48'', ''SYBASE'', ''Sybase'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''49'', ''SYBASEIQ'', ''SybaseIQ'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''50'', ''TERADATA'', ''Teradata'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''51'', ''UNIVERSE'', ''UniVerse database'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''52'', ''VERTICA'', ''Vertica'', NULL, NULL, NULL, NULL, ''0'');
INSERT INTO K_DATABASE_TYPE
VALUES (''53'', ''VERTICA5'', ''Vertica 5+'', NULL, NULL, NULL, NULL, ''0'');

-- ----------------------------
-- Table structure for K_DATASOURCE_USER
-- ----------------------------
DROP TABLE K_DATASOURCE_USER;
CREATE TABLE K_DATASOURCE_USER
(
    ID             VARCHAR2(32 BYTE) DEFAULT sys_guid()
        NOT NULL,
    USERNAME       VARCHAR2(30 BYTE),
    PASSWORD       VARCHAR2(30 BYTE),
    TABLESPACE     VARCHAR2(30 BYTE),
    ADMDIVCODE     VARCHAR2(12 BYTE),
    DB_TYPE        VARCHAR2(50 BYTE),
    SYSTEM_ID      VARCHAR2(32 BYTE),
    DATABASE_NAME  VARCHAR2(20 BYTE),
    DATABASE_PORT  NUMBER,
    DATABASE_HOST  VARCHAR2(15 BYTE),
    LAST_IMPL_DATE DATE
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_DATASOURCE_USER.TABLESPACE IS ''表空间'';
COMMENT
ON COLUMN K_DATASOURCE_USER.DB_TYPE IS ''数据库类型'';
COMMENT
ON COLUMN K_DATASOURCE_USER.LAST_IMPL_DATE IS ''最后导入日期'';
COMMENT
ON TABLE K_DATASOURCE_USER IS ''备份文件恢复用户表'';

-- ----------------------------
-- Records of K_DATASOURCE_USER
-- ----------------------------

-- ----------------------------
-- Table structure for K_FTP
-- ----------------------------
DROP TABLE K_FTP;
CREATE TABLE K_FTP
(
    ID                VARCHAR2(32 BYTE) DEFAULT sys_guid()
        NOT NULL,
    HOST              VARCHAR2(15 BYTE),
    USERNAME          VARCHAR2(30 BYTE),
    PASSWORD          VARCHAR2(30 BYTE),
    PORT              NUMBER(5),
    CHARSET           VARCHAR2(30 BYTE),
    SKIP_DIR          VARCHAR2(300 BYTE),
    FILE_FILTER       VARCHAR2(20 BYTE),
    DIR               VARCHAR2(50 BYTE),
    SYSTEM_ID         VARCHAR2(32 BYTE),
    FILE_TYPE         VARCHAR2(20 BYTE),
    IMPL_TYPE         VARCHAR2(20 BYTE),
    FILE_NAME_REGULAR VARCHAR2(100 BYTE),
    LOCAL_FILE_PATH   VARCHAR2(200 BYTE)
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_FTP.SKIP_DIR IS ''跳过目录，多目录使用英文逗号隔开'';
COMMENT
ON COLUMN K_FTP.FILE_FILTER IS ''仅匹配当前结尾的文件'';
COMMENT
ON COLUMN K_FTP.DIR IS ''根文件夹'';
COMMENT
ON COLUMN K_FTP.SYSTEM_ID IS ''关联系统ID'';
COMMENT
ON COLUMN K_FTP.FILE_TYPE IS ''文件类型'';
COMMENT
ON COLUMN K_FTP.IMPL_TYPE IS ''文件导入方式（exp、expdp）'';
COMMENT
ON COLUMN K_FTP.FILE_NAME_REGULAR IS ''文件名称正则表达式'';
COMMENT
ON COLUMN K_FTP.LOCAL_FILE_PATH IS ''下载文件地址'';

-- ----------------------------
-- Table structure for K_FTP_FILE
-- ----------------------------
DROP TABLE K_FTP_FILE;
CREATE TABLE K_FTP_FILE
(
    ID                VARCHAR2(32 BYTE) DEFAULT sys_guid()
        NOT NULL,
    FILE_NAME         VARCHAR2(100 BYTE),
    FILE_NAME_REGULAR VARCHAR2(100 BYTE),
    FILE_PATH         VARCHAR2(300 BYTE),
    FILE_SIZE         LONG,
    FILE_TIME         TIMESTAMP(6),
    ERROR_MESSAGE     CLOB,
    STATUS            VARCHAR2(2 BYTE),
    FTP_ID            VARCHAR2(32 BYTE),
    BATCH_NO          VARCHAR2(30 BYTE)
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_FTP_FILE.FILE_SIZE IS ''文件大小，单位MB'';
COMMENT
ON COLUMN K_FTP_FILE.BATCH_NO IS ''批次号'';

-- ----------------------------
-- Table structure for K_JOB
-- ----------------------------
DROP TABLE K_JOB;
CREATE TABLE K_JOB
(
    ID                NUMBER(11) NOT NULL,
    CATEGORY_ID       NUMBER(11),
    JOB_NAME          NVARCHAR2(50),
    JOB_DESCRIPTION   NVARCHAR2(500),
    JOB_TYPE          NVARCHAR2(10),
    JOB_PATH          NVARCHAR2(200),
    JOB_REPOSITORY_ID NUMBER(11),
    JOB_QUARTZ        NUMBER(11),
    SYNC_STRATEGY     NVARCHAR2(50),
    JOB_LOG_LEVEL     NVARCHAR2(10),
    JOB_STATUS        NUMBER(11),
    ADD_TIME          DATE,
    ADD_USER          NUMBER(11),
    EDIT_TIME         DATE,
    EDIT_USER         NUMBER(11),
    DEL_FLAG          NUMBER(11),
    FTP_ID            VARCHAR2(32 BYTE)
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_JOB.ID IS ''作业ID'';
COMMENT
ON COLUMN K_JOB.CATEGORY_ID IS ''分类ID（外键ID）'';
COMMENT
ON COLUMN K_JOB.JOB_NAME IS ''作业名称'';
COMMENT
ON COLUMN K_JOB.JOB_DESCRIPTION IS ''任务描述'';
COMMENT
ON COLUMN K_JOB.JOB_TYPE IS ''执行类型（rep：资源库；file：文件；ftp：FTP）'';
COMMENT
ON COLUMN K_JOB.JOB_PATH IS ''作业保存路径（可以是资源库中的路径也可以是服务器中保存作业文件的路径）'';
COMMENT
ON COLUMN K_JOB.JOB_REPOSITORY_ID IS ''作业的资源库ID'';
COMMENT
ON COLUMN K_JOB.JOB_QUARTZ IS ''定时策略（外键ID）'';
COMMENT
ON COLUMN K_JOB.SYNC_STRATEGY IS ''同步策略(T+n)'';
COMMENT
ON COLUMN K_JOB.JOB_LOG_LEVEL IS ''日志级别(Basic，Detailed，Error，Debug，Minimal，Rowlevel）'';
COMMENT ON COLUMN K_JOB.JOB_STATUS IS ''状态（1：正在运行；2：已停止）'';
COMMENT ON COLUMN K_JOB.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_JOB.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_JOB.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_JOB.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_JOB.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON COLUMN K_JOB.FTP_ID IS ''FTP账号'';
COMMENT ON TABLE K_JOB IS ''作业表'';

-- ----------------------------
-- Table structure for K_JOB_MONITOR
-- ----------------------------
DROP TABLE K_JOB_MONITOR;
CREATE TABLE K_JOB_MONITOR
(
    ID                NUMBER(11) NOT NULL,
    MONITOR_JOB_ID    NUMBER(11),
    MONITOR_SUCCESS   NUMBER(11),
    MONITOR_FAIL      NUMBER(11),
    MONITOR_STATUS    NUMBER(11),
    RUN_STATUS        NCLOB,
    LAST_EXECUTE_TIME DATE,
    NEXT_EXECUTE_TIME DATE,
    ADD_TIME          DATE,
    ADD_USER          NUMBER(11),
    EDIT_TIME         DATE,
    EDIT_USER         NUMBER(11),
    DEL_FLAG          NUMBER(11)
)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_JOB_MONITOR.ID IS ''监控作业ID'';
COMMENT ON COLUMN K_JOB_MONITOR.MONITOR_JOB_ID IS ''监控的作业ID'';
COMMENT ON COLUMN K_JOB_MONITOR.MONITOR_SUCCESS IS ''成功次数'';
COMMENT ON COLUMN K_JOB_MONITOR.MONITOR_FAIL IS ''失败次数'';
COMMENT ON COLUMN K_JOB_MONITOR.MONITOR_STATUS IS ''监控状态（是否启动，1:启动；2:停止）'';
COMMENT ON COLUMN K_JOB_MONITOR.RUN_STATUS IS ''运行状态（起始时间-结束时间,起始时间-结束时间……）'';
COMMENT ON COLUMN K_JOB_MONITOR.LAST_EXECUTE_TIME IS ''上一次执行时间'';
COMMENT ON COLUMN K_JOB_MONITOR.NEXT_EXECUTE_TIME IS ''下一次执行时间'';
COMMENT ON COLUMN K_JOB_MONITOR.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_JOB_MONITOR.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_JOB_MONITOR.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_JOB_MONITOR.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_JOB_MONITOR.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON TABLE K_JOB_MONITOR IS ''作业监控表'';

-- ----------------------------
-- Table structure for K_JOB_RECORD
-- ----------------------------
DROP TABLE K_JOB_RECORD;
CREATE TABLE K_JOB_RECORD
(
    ID            NUMBER(11) NOT NULL,
    RECORD_JOB_ID NUMBER(11),
    START_TIME    DATE,
    STOP_TIME     DATE,
    RECORD_STATUS NUMBER(11),
    LOG_FILE_PATH NVARCHAR2(100),
    ADD_TIME      DATE,
    ADD_USER      NUMBER(11),
    EDIT_TIME     DATE,
    EDIT_USER     NUMBER(11),
    DEL_FLAG      NUMBER(11)
)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_JOB_RECORD.ID IS ''作业记录ID'';
COMMENT ON COLUMN K_JOB_RECORD.RECORD_JOB_ID IS ''作业ID'';
COMMENT ON COLUMN K_JOB_RECORD.START_TIME IS ''启动时间'';
COMMENT ON COLUMN K_JOB_RECORD.STOP_TIME IS ''停止时间'';
COMMENT ON COLUMN K_JOB_RECORD.RECORD_STATUS IS ''任务执行结果（1：成功；0：失败）'';
COMMENT ON COLUMN K_JOB_RECORD.LOG_FILE_PATH IS ''作业日志记录文件保存位置'';
COMMENT ON COLUMN K_JOB_RECORD.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_JOB_RECORD.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_JOB_RECORD.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_JOB_RECORD.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_JOB_RECORD.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON TABLE K_JOB_RECORD IS ''作业执行记录表'';

-- ----------------------------
-- Table structure for K_MVIEW
-- ----------------------------
DROP TABLE K_MVIEW;
CREATE TABLE K_MVIEW
(
    ID                VARCHAR2(32 BYTE) DEFAULT sys_guid() NOT NULL,
    OWNER             VARCHAR2(128 BYTE)                   NOT NULL,
    MVIEW_NAME        VARCHAR2(200 BYTE)                   NOT NULL,
    LAST_REFRESH_DATE DATE,
    REFRESH_METHOD    VARCHAR2(8 BYTE),
    INVALID           VARCHAR2(1 BYTE),
    MVIEW_TAG_ID      VARCHAR2(32 BYTE),
    QUERY             LONG                                 NOT NULL,
    STATUS            NUMBER(1)         DEFAULT 1

)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_MVIEW.STATUS IS ''是否有效：1（启用）、0（停用）'';

-- ----------------------------
-- Table structure for K_MVIEW_TAG
-- ----------------------------
DROP TABLE K_MVIEW_TAG;
CREATE TABLE K_MVIEW_TAG
(
    ID             VARCHAR2(32 BYTE) DEFAULT sys_guid()
                                        NOT NULL,
    MVIEW_TAG_CODE VARCHAR2(40 BYTE)  NOT NULL,
    MVIEW_TAG_NAME VARCHAR2(100 BYTE) NOT NULL,
    PARENT_ID      VARCHAR2(32 BYTE) DEFAULT 0

)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_MVIEW_TAG.MVIEW_TAG_CODE IS ''物化视图标签'';
COMMENT ON COLUMN K_MVIEW_TAG.MVIEW_TAG_NAME IS ''标签名称'';

-- ----------------------------
-- Table structure for K_QUARTZ
-- ----------------------------
DROP TABLE K_QUARTZ;
CREATE TABLE K_QUARTZ
(
    ID                 NUMBER(11) NOT NULL,
    QUARTZ_DESCRIPTION NVARCHAR2(500),
    QUARTZ_CRON        NVARCHAR2(100),
    ADD_TIME           DATE,
    ADD_USER           NUMBER(11),
    EDIT_TIME          DATE,
    EDIT_USER          NUMBER(11),
    DEL_FLAG           NUMBER(11)
)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_QUARTZ.ID IS ''任务ID'';
COMMENT ON COLUMN K_QUARTZ.QUARTZ_DESCRIPTION IS ''任务描述'';
COMMENT ON COLUMN K_QUARTZ.QUARTZ_CRON IS ''定时策略'';
COMMENT ON COLUMN K_QUARTZ.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_QUARTZ.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_QUARTZ.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_QUARTZ.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_QUARTZ.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON TABLE K_QUARTZ IS ''定时任务表'';

-- ----------------------------
-- Records of K_QUARTZ
-- ----------------------------
INSERT INTO K_QUARTZ
VALUES (''2'', ''每周一0点执行一次'', ''0 0 0 ? * 2'', TO_DATE(''2017-05-27 14:56:38'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'',
        TO_DATE(''2017-05-27 14:56:38'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'', ''0'');
INSERT INTO K_QUARTZ
VALUES (''3'', ''每月1日0点执行一次'', ''0 0 0 1 * ?'', TO_DATE(''2017-05-27 14:56:38'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'',
        TO_DATE(''2017-05-27 14:56:38'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'', ''0'');
INSERT INTO K_QUARTZ
VALUES (''4'', ''每日0点执行一次'', ''0 0 0 * * ?'', TO_DATE(''2017-05-27 14:44:13'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'',
        TO_DATE(''2017-05-27 14:44:15'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'', ''0'');
INSERT INTO K_QUARTZ
VALUES (''5'', ''每日两点执行一次'', ''0 0 2 * * ?'', TO_DATE(''2019-12-24 17:43:18'', ''SYYYY-MM-DD HH24:MI:SS''), NULL,
        TO_DATE(''2019-12-29 18:03:55'', ''SYYYY-MM-DD HH24:MI:SS''), NULL, ''0'');
INSERT INTO K_QUARTZ
VALUES (''-20'', ''省本级支付数据下载'', ''0 0,50 0 1,8,15,22,29 * ?'', TO_DATE(''2019-12-30 22:46:38'', ''SYYYY-MM-DD HH24:MI:SS''), NULL,
        TO_DATE(''2019-12-30 22:46:38'', ''SYYYY-MM-DD HH24:MI:SS''), NULL, ''0'');
INSERT INTO K_QUARTZ
VALUES (''-19'', ''地市支付数据下载'', ''0 50 1 1,15 * ?'', TO_DATE(''2019-12-30 22:47:25'', ''SYYYY-MM-DD HH24:MI:SS''), NULL,
        TO_DATE(''2019-12-30 22:47:25'', ''SYYYY-MM-DD HH24:MI:SS''), NULL, ''0'');
INSERT INTO K_QUARTZ
VALUES (''1'', ''立即执行一次'', NULL, NULL, ''1'', NULL, ''1'', ''0'');

-- ----------------------------
-- Table structure for K_REPOSITORY
-- ----------------------------
DROP TABLE K_REPOSITORY;
CREATE TABLE K_REPOSITORY
(
    ID            VARCHAR2(32 BYTE) DEFAULT sys_guid()
        NOT NULL,
    REP_NAME      NVARCHAR2(50),
    REP_TYPE      NVARCHAR2(10),
    REP_USERNAME  NVARCHAR2(50),
    REP_PASSWORD  NVARCHAR2(50),
    REP_BASE_PATH NVARCHAR2(500),
    DB_TYPE       NVARCHAR2(10),
    DB_ACCESS     NVARCHAR2(10),
    DB_HOST       NVARCHAR2(50),
    DB_PORT       NVARCHAR2(10),
    DB_NAME       NVARCHAR2(20),
    DB_USERNAME   NVARCHAR2(50),
    DB_PASSWORD   NVARCHAR2(50),
    ADD_TIME      DATE,
    ADD_USER      NUMBER(11),
    EDIT_TIME     DATE,
    EDIT_USER     NUMBER(11),
    DEL_FLAG      NUMBER(11)
)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_REPOSITORY.ID IS ''ID'';
COMMENT ON COLUMN K_REPOSITORY.REP_NAME IS ''资源库名称'';
COMMENT ON COLUMN K_REPOSITORY.REP_TYPE IS ''资源库类型: fileRep-文件, dbRep-数据库'';
COMMENT ON COLUMN K_REPOSITORY.REP_USERNAME IS ''登录用户名'';
COMMENT ON COLUMN K_REPOSITORY.REP_PASSWORD IS ''登录密码'';
COMMENT ON COLUMN K_REPOSITORY.REP_BASE_PATH IS ''文件资源库路径: rep_type=fileRep生效'';
COMMENT ON COLUMN K_REPOSITORY.DB_TYPE IS ''资源库数据库类型（MYSQL、ORACLE）'';
COMMENT ON COLUMN K_REPOSITORY.DB_ACCESS IS ''资源库数据库访问模式（Native, ODBC, OCI, Plugin, JNDI)'';
COMMENT
ON COLUMN K_REPOSITORY.DB_HOST IS ''资源库数据库主机名或者IP地址'';
COMMENT
ON COLUMN K_REPOSITORY.DB_PORT IS ''资源库数据库端口号'';
COMMENT
ON COLUMN K_REPOSITORY.DB_NAME IS ''资源库数据库名称'';
COMMENT
ON COLUMN K_REPOSITORY.DB_USERNAME IS ''数据库登录账号'';
COMMENT
ON COLUMN K_REPOSITORY.DB_PASSWORD IS ''数据库登录密码'';
COMMENT
ON COLUMN K_REPOSITORY.ADD_TIME IS ''添加时间'';
COMMENT
ON COLUMN K_REPOSITORY.ADD_USER IS ''添加者'';
COMMENT
ON COLUMN K_REPOSITORY.EDIT_TIME IS ''编辑时间'';
COMMENT
ON COLUMN K_REPOSITORY.EDIT_USER IS ''编辑者'';
COMMENT
ON COLUMN K_REPOSITORY.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT
ON TABLE K_REPOSITORY IS ''资源库表'';

-- ----------------------------
-- Records of K_REPOSITORY
-- ----------------------------
INSERT INTO K_REPOSITORY
VALUES ('' - 18 '', ''kettle'', ''dbRep'', ''admin'', ''admin'', NULL, ''ORACLE'', ''Native'', ''10.108.8.17'',
        ''1521'', ''orcl'', ''kettle'',
        ''123456'', TO_DATE(''2019-12-30 23:45:27'', ''SYYYY-MM-DD HH24:MI:SS''), NULL,
        TO_DATE(''2020-04-17 10:25:24'', ''SYYYY-MM-DD HH24:MI:SS''), NULL, ''0'');

-- ----------------------------
-- Table structure for K_SYSTEM
-- ----------------------------
DROP TABLE K_SYSTEM;
CREATE TABLE K_SYSTEM
(
    ID          VARCHAR2(32 BYTE) DEFAULT sys_guid()
        NOT NULL,
    SYSTEM_NAME VARCHAR2(50 BYTE),
    TAG_ID      VARCHAR2(32 BYTE),
    DATASOURCE  VARCHAR2(50 BYTE),
    REMARK      VARCHAR2(500 BYTE)
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_SYSTEM.TAG_ID IS ''标识ID，一直贯穿，某一类型数据'';
COMMENT
ON COLUMN K_SYSTEM.DATASOURCE IS ''数据来源'';

-- ----------------------------
-- Records of K_SYSTEM
-- ----------------------------
INSERT INTO K_SYSTEM
VALUES (''9B7031EA564E6C53E055000000000001'', ''非税系统'', ''9AA9A958CBEC42CAE055000000000001'', ''镜像库'', NULL);
INSERT INTO K_SYSTEM
VALUES (''9AE90047A1A959E0E055000000000001'', ''支付系统'', ''9AA9A958CBEB42CAE055000000000001'', ''数据库'', NULL);

-- ----------------------------
-- Table structure for K_TASK_LOG
-- ----------------------------
DROP TABLE K_TASK_LOG;
CREATE TABLE K_TASK_LOG
(
    ID           VARCHAR2(32 BYTE) DEFAULT sys_guid() NOT NULL,
    TASK_TYPE    VARCHAR2(50 BYTE) NOT NULL,
    TASK_STATUS  NUMBER NOT NULL,
    TASK_MESSAGE VARCHAR2(1000 BYTE) NOT NULL,
    TASK_DATE    DATE   NOT NULL
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_TASK_LOG.TASK_TYPE IS ''任务类型：文件下载、文件导入、视图刷新等'';
COMMENT
ON COLUMN K_TASK_LOG.TASK_STATUS IS ''0、成功；-1、失败'';

-- ----------------------------
-- Table structure for K_TRANS
-- ----------------------------
DROP TABLE K_TRANS;
CREATE TABLE K_TRANS
(
    ID                  NUMBER(11) NOT NULL,
    CATEGORY_ID         NUMBER(11),
    TRANS_NAME          NVARCHAR2(50),
    TRANS_DESCRIPTION   NVARCHAR2(500),
    TRANS_TYPE          NVARCHAR2(10),
    TRANS_PATH          NVARCHAR2(200),
    TRANS_REPOSITORY_ID NUMBER(11),
    TRANS_QUARTZ        NUMBER(11),
    SYNC_STRATEGY       NVARCHAR2(50),
    TRANS_LOG_LEVEL     NVARCHAR2(10),
    TRANS_STATUS        NUMBER(11),
    ADD_TIME            DATE,
    ADD_USER            NUMBER(11),
    EDIT_TIME           DATE,
    EDIT_USER           NUMBER(11),
    DEL_FLAG            NUMBER(11),
    TRANS_PARAMS        VARCHAR2(500 BYTE)
) TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT
ON COLUMN K_TRANS.ID IS ''转换ID'';
COMMENT
ON COLUMN K_TRANS.CATEGORY_ID IS ''分类ID'';
COMMENT
ON COLUMN K_TRANS.TRANS_NAME IS ''转换名称'';
COMMENT
ON COLUMN K_TRANS.TRANS_DESCRIPTION IS ''转换描述'';
COMMENT
ON COLUMN K_TRANS.TRANS_TYPE IS ''执行类型（rep：资源库；file：文件）'';
COMMENT
ON COLUMN K_TRANS.TRANS_PATH IS ''转换保存路径（可以是资源库中的路径也可以是服务器中保存作业文件的路径）'';
COMMENT
ON COLUMN K_TRANS.TRANS_REPOSITORY_ID IS ''转换的资源库ID'';
COMMENT
ON COLUMN K_TRANS.TRANS_QUARTZ IS ''定时策略（外键ID）'';
COMMENT
ON COLUMN K_TRANS.SYNC_STRATEGY IS ''同步策略(T+n)'';
COMMENT
ON COLUMN K_TRANS.TRANS_LOG_LEVEL IS ''日志级别(Basic，Detailed，Error，Debug，Minimal，Rowlevel）'';
COMMENT ON COLUMN K_TRANS.TRANS_STATUS IS ''状态（1：正在运行；2：已停止）'';
COMMENT ON COLUMN K_TRANS.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_TRANS.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_TRANS.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_TRANS.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_TRANS.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON COLUMN K_TRANS.TRANS_PARAMS IS ''转换参数'';
COMMENT ON TABLE K_TRANS IS ''转换表'';


-- ----------------------------
-- Table structure for K_TRANS_MONITOR
-- ----------------------------
DROP TABLE K_TRANS_MONITOR;
CREATE TABLE K_TRANS_MONITOR
(
    ID                NUMBER(11) NOT NULL,
    MONITOR_TRANS_ID  NUMBER(11),
    MONITOR_SUCCESS   NUMBER(11),
    MONITOR_FAIL      NUMBER(11),
    MONITOR_STATUS    NUMBER(11),
    RUN_STATUS        NCLOB,
    LAST_EXECUTE_TIME DATE,
    NEXT_EXECUTE_TIME DATE,
    ADD_TIME          DATE,
    ADD_USER          NUMBER(11),
    EDIT_TIME         DATE,
    EDIT_USER         NUMBER(11),
    DEL_FLAG          NUMBER(11)
)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_TRANS_MONITOR.ID IS ''监控转换ID'';
COMMENT ON COLUMN K_TRANS_MONITOR.MONITOR_TRANS_ID IS ''监控的转换的ID'';
COMMENT ON COLUMN K_TRANS_MONITOR.MONITOR_SUCCESS IS ''成功次数'';
COMMENT ON COLUMN K_TRANS_MONITOR.MONITOR_FAIL IS ''失败次数'';
COMMENT ON COLUMN K_TRANS_MONITOR.MONITOR_STATUS IS ''监控状态（是否启动，1:启动；2:停止）'';
COMMENT ON COLUMN K_TRANS_MONITOR.RUN_STATUS IS ''运行状态（起始时间-结束时间,起始时间-结束时间……）'';
COMMENT ON COLUMN K_TRANS_MONITOR.LAST_EXECUTE_TIME IS ''上一次执行时间'';
COMMENT ON COLUMN K_TRANS_MONITOR.NEXT_EXECUTE_TIME IS ''下一次执行时间'';
COMMENT ON COLUMN K_TRANS_MONITOR.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_TRANS_MONITOR.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_TRANS_MONITOR.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_TRANS_MONITOR.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_TRANS_MONITOR.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON TABLE K_TRANS_MONITOR IS ''转换监控表'';

-- ----------------------------
-- Table structure for K_TRANS_RECORD
-- ----------------------------
DROP TABLE K_TRANS_RECORD;
CREATE TABLE K_TRANS_RECORD
(
    ID              NUMBER(11) NOT NULL,
    RECORD_TRANS_ID NUMBER(11),
    START_TIME      DATE,
    STOP_TIME       DATE,
    RECORD_STATUS   NUMBER(11),
    LOG_FILE_PATH   NVARCHAR2(100),
    ADD_TIME        DATE,
    ADD_USER        NUMBER(11),
    EDIT_TIME       DATE,
    EDIT_USER       NUMBER(11),
    DEL_FLAG        NUMBER(11)
)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_TRANS_RECORD.ID IS ''转换记录ID'';
COMMENT ON COLUMN K_TRANS_RECORD.RECORD_TRANS_ID IS ''转换ID'';
COMMENT ON COLUMN K_TRANS_RECORD.START_TIME IS ''启动时间'';
COMMENT ON COLUMN K_TRANS_RECORD.STOP_TIME IS ''停止时间'';
COMMENT ON COLUMN K_TRANS_RECORD.RECORD_STATUS IS ''任务执行结果（1：成功；0：失败）'';
COMMENT ON COLUMN K_TRANS_RECORD.LOG_FILE_PATH IS ''转换日志记录文件保存位置'';
COMMENT ON COLUMN K_TRANS_RECORD.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_TRANS_RECORD.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_TRANS_RECORD.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_TRANS_RECORD.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_TRANS_RECORD.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON TABLE K_TRANS_RECORD IS ''转换执行记录表'';

-- ----------------------------
-- Table structure for K_USER
-- ----------------------------
DROP TABLE K_USER;
CREATE TABLE K_USER
(
    ID        NUMBER(11) NOT NULL,
    NICKNAME  NVARCHAR2(50),
    EMAIL     NVARCHAR2(30),
    PHONE     NVARCHAR2(50),
    ACCOUNT   NVARCHAR2(50),
    PASSWORD  NVARCHAR2(500),
    ADD_TIME  DATE,
    ADD_USER  NUMBER(11),
    EDIT_TIME DATE,
    EDIT_USER NUMBER(11),
    DEL_FLAG  NUMBER(11)
)
    TABLESPACE KETTLE
    LOGGING
    NOCOMPRESS
    PCTFREE 10
    INITRANS 1
    STORAGE
(
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
)
    PARALLEL 1
    NOCACHE
    DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN K_USER.ID IS ''用户ID'';
COMMENT ON COLUMN K_USER.NICKNAME IS ''用户昵称'';
COMMENT ON COLUMN K_USER.EMAIL IS ''用户邮箱'';
COMMENT ON COLUMN K_USER.PHONE IS ''用于电话'';
COMMENT ON COLUMN K_USER.ACCOUNT IS ''用户账号'';
COMMENT ON COLUMN K_USER.PASSWORD IS ''用户密码'';
COMMENT ON COLUMN K_USER.ADD_TIME IS ''添加时间'';
COMMENT ON COLUMN K_USER.ADD_USER IS ''添加者'';
COMMENT ON COLUMN K_USER.EDIT_TIME IS ''编辑时间'';
COMMENT ON COLUMN K_USER.EDIT_USER IS ''编辑者'';
COMMENT ON COLUMN K_USER.DEL_FLAG IS ''是否删除（0：存在；1：删除）'';
COMMENT ON TABLE K_USER IS ''用户表'';

-- ----------------------------
-- Records of K_USER
-- ----------------------------
INSERT INTO K_USER
VALUES (''1'', ''管理员'', NULL, NULL, ''admin'', ''00c952ef1f15bf47c1af8d35a0ccaa4620d12e018734ef63a37cf379050b117c'',
        TO_DATE(''2019-11-20 20:11:53'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'',
        TO_DATE(''2019-12-06 11:13:38'', ''SYYYY-MM-DD HH24:MI:SS''), ''1'', ''1'');

-- ----------------------------
-- Table structure for TEST
-- ----------------------------
DROP TABLE K_LOG;
create table K_LOG
(
    ID          VARCHAR2(32),
    ADMDIVCODE  VARCHAR2(255),
    TYPE        VARCHAR2(255),
    TRANSNAME   VARCHAR2(255),
    STEPNAME    VARCHAR2(255),
    I           NUMBER(32),
    O           NUMBER(32),
    R           NUMBER(32),
    W           NUMBER(32),
    U           NUMBER(32),
    E           NUMBER(32),
    TIME        DATE,
    CATEGORY_ID NUMBER(32)
);

comment on column K_LOG.ID is ''id'';

comment on column K_LOG.ADMDIVCODE is ''区划code'';

comment on column K_LOG.TYPE is ''1为读2为写3为其他'';

comment on column K_LOG.TRANSNAME is ''转换名'';

comment on column K_LOG.STEPNAME is ''步骤名'';

comment on column K_LOG.I is ''当前步骤生成的记录数（从表输出、文件读入）'';

comment on column K_LOG.O is ''当前步骤输出的记录数（输出的文件和表） '';

comment on column K_LOG.R is ''当前步骤从前一步骤读取的记录数 '';

comment on column K_LOG.W is ''当前步骤向后面步骤抛出的记录数 '';

comment on column K_LOG.U is ''当前步骤更新过的记录数 '';

comment on column K_LOG.E is ''当前步骤处理的记录数'';

comment on column K_LOG.TIME is ''完成时间''

comment on column K_LOG.CATEGORY_ID is ''分类id''



-- ----------------------------
-- Sequence structure for SEQ_TRANS
-- ----------------------------
DROP SEQUENCE SEQ_TRANS;
CREATE SEQUENCE SEQ_TRANS MINVALUE 1 MAXVALUE 99999999 INCREMENT BY 1 CACHE 20;

-- ----------------------------
-- Primary Key structure for table K_CATEGORY
-- ----------------------------
ALTER TABLE K_CATEGORY
    ADD CONSTRAINT SYS_C0019605 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_CATEGORY
-- ----------------------------
ALTER TABLE K_CATEGORY
    ADD CONSTRAINT SYS_C0019594 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Indexes structure for table K_CATEGORY
-- ----------------------------
CREATE UNIQUE INDEX CATEGORY_NAME_UNIQUE_INDEX
    ON K_CATEGORY (CATEGORY_NAME ASC)
    LOGGING
    TABLESPACE KETTLE
    VISIBLE
    PCTFREE 10
    INITRANS 2
    STORAGE (
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
    );

-- ----------------------------
-- Primary Key structure for table K_DATABASE_TYPE
-- ----------------------------
ALTER TABLE K_DATABASE_TYPE
    ADD CONSTRAINT SYS_C0019606 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_DATABASE_TYPE
-- ----------------------------
ALTER TABLE K_DATABASE_TYPE
    ADD CONSTRAINT SYS_C0019595 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_DATASOURCE_USER
-- ----------------------------
ALTER TABLE K_DATASOURCE_USER
    ADD CONSTRAINT SYS_C0019651 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_DATASOURCE_USER
-- ----------------------------
ALTER TABLE K_DATASOURCE_USER
    ADD CONSTRAINT SYS_C0019644 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_FTP
-- ----------------------------
ALTER TABLE K_FTP
    ADD CONSTRAINT SYS_C0019647 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_FTP
-- ----------------------------
ALTER TABLE K_FTP
    ADD CONSTRAINT SYS_C0019646 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_FTP_FILE
-- ----------------------------
ALTER TABLE K_FTP_FILE
    ADD CONSTRAINT SYS_C0019650 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_FTP_FILE
-- ----------------------------
ALTER TABLE K_FTP_FILE
    ADD CONSTRAINT SYS_C0019649 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_JOB
-- ----------------------------
ALTER TABLE K_JOB
    ADD CONSTRAINT SYS_C0019607 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_JOB
-- ----------------------------
ALTER TABLE K_JOB
    ADD CONSTRAINT SYS_C0019596 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Indexes structure for table K_JOB
-- ----------------------------
CREATE UNIQUE INDEX JOB_NAME_UNIQUE_INDEX
    ON K_JOB (JOB_NAME ASC)
    LOGGING
    TABLESPACE KETTLE
    VISIBLE
    PCTFREE 10
    INITRANS 2
    STORAGE (
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
    );

-- ----------------------------
-- Primary Key structure for table K_JOB_MONITOR
-- ----------------------------
ALTER TABLE K_JOB_MONITOR
    ADD CONSTRAINT SYS_C0019608 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_JOB_MONITOR
-- ----------------------------
ALTER TABLE K_JOB_MONITOR
    ADD CONSTRAINT SYS_C0019597 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_JOB_RECORD
-- ----------------------------
ALTER TABLE K_JOB_RECORD
    ADD CONSTRAINT SYS_C0019609 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_JOB_RECORD
-- ----------------------------
ALTER TABLE K_JOB_RECORD
    ADD CONSTRAINT SYS_C0019598 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Checks structure for table K_MVIEW
-- ----------------------------
ALTER TABLE K_MVIEW
    ADD CONSTRAINT SYS_C0019619 CHECK (OWNER IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_MVIEW
    ADD CONSTRAINT SYS_C0019620 CHECK (MVIEW_NAME IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_MVIEW
    ADD CONSTRAINT SYS_C0019621 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_MVIEW
    ADD CONSTRAINT SYS_C0019623 CHECK (QUERY IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Checks structure for table K_MVIEW_TAG
-- ----------------------------
ALTER TABLE K_MVIEW_TAG
    ADD CONSTRAINT SYS_C0019616 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_MVIEW_TAG
    ADD CONSTRAINT SYS_C0019617 CHECK (MVIEW_TAG_CODE IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_MVIEW_TAG
    ADD CONSTRAINT SYS_C0019618 CHECK (MVIEW_TAG_NAME IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_QUARTZ
-- ----------------------------
ALTER TABLE K_QUARTZ
    ADD CONSTRAINT SYS_C0019610 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_QUARTZ
-- ----------------------------
ALTER TABLE K_QUARTZ
    ADD CONSTRAINT SYS_C0019599 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_REPOSITORY
-- ----------------------------
ALTER TABLE K_REPOSITORY
    ADD CONSTRAINT SYS_C0019611 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_REPOSITORY
-- ----------------------------
ALTER TABLE K_REPOSITORY
    ADD CONSTRAINT SYS_C0019600 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Indexes structure for table K_REPOSITORY
-- ----------------------------
CREATE UNIQUE INDEX REP_NAME_UNIQUE_INDEX
    ON K_REPOSITORY (REP_NAME ASC)
    LOGGING
    TABLESPACE KETTLE
    VISIBLE
    PCTFREE 10
    INITRANS 2
    STORAGE (
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
    );

-- ----------------------------
-- Primary Key structure for table K_SYSTEM
-- ----------------------------
ALTER TABLE K_SYSTEM
    ADD CONSTRAINT SYS_C0019643 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_SYSTEM
-- ----------------------------
ALTER TABLE K_SYSTEM
    ADD CONSTRAINT SYS_C0019642 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Checks structure for table K_TASK_LOG
-- ----------------------------
ALTER TABLE K_TASK_LOG
    ADD CONSTRAINT SYS_C0019652 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_TASK_LOG
    ADD CONSTRAINT SYS_C0019653 CHECK (TASK_TYPE IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_TASK_LOG
    ADD CONSTRAINT SYS_C0019654 CHECK (TASK_STATUS IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_TASK_LOG
    ADD CONSTRAINT SYS_C0019655 CHECK (TASK_MESSAGE IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE K_TASK_LOG
    ADD CONSTRAINT SYS_C0019656 CHECK (TASK_DATE IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_TRANS
-- ----------------------------
ALTER TABLE K_TRANS
    ADD CONSTRAINT SYS_C0019612 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_TRANS
-- ----------------------------
ALTER TABLE K_TRANS
    ADD CONSTRAINT SYS_C0019601 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Triggers structure for table K_TRANS
-- ----------------------------
CREATE TRIGGER TRANS_TRIGGER
    BEFORE INSERT
    ON K_TRANS
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
begin
    select SEQ_TRANS.Nextval into :new.id from dual;
end;
/

-- ----------------------------
-- Primary Key structure for table K_TRANS_MONITOR
-- ----------------------------
ALTER TABLE K_TRANS_MONITOR
    ADD CONSTRAINT SYS_C0019613 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_TRANS_MONITOR
-- ----------------------------
ALTER TABLE K_TRANS_MONITOR
    ADD CONSTRAINT SYS_C0019602 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_TRANS_RECORD
-- ----------------------------
ALTER TABLE K_TRANS_RECORD
    ADD CONSTRAINT SYS_C0019614 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_TRANS_RECORD
-- ----------------------------
ALTER TABLE K_TRANS_RECORD
    ADD CONSTRAINT SYS_C0019603 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Primary Key structure for table K_USER
-- ----------------------------
ALTER TABLE K_USER
    ADD CONSTRAINT SYS_C0019615 PRIMARY KEY (ID);

-- ----------------------------
-- Checks structure for table K_USER
-- ----------------------------
ALTER TABLE K_USER
    ADD CONSTRAINT SYS_C0019604 CHECK (ID IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

-- ----------------------------
-- Indexes structure for table K_USER
-- ----------------------------
CREATE UNIQUE INDEX UNIQUE_INDEX_ACCOUNT
    ON K_USER (ACCOUNT ASC)
    LOGGING
    TABLESPACE KETTLE
    VISIBLE
    PCTFREE 10
    INITRANS 2
    STORAGE (
    INITIAL 65536
    NEXT 1048576
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL DEFAULT
    );

-- ----------------------------
-- Foreign Keys structure for table K_FTP
-- ----------------------------
ALTER TABLE K_FTP
    ADD CONSTRAINT FK_K_FTP_K_SYSTEM_1 FOREIGN KEY (SYSTEM_ID) REFERENCES K_SYSTEM (ID) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;


-- ----------------------------
-- 首页任务执行情况统计视图
-- ----------------------------
CREATE OR REPLACE VIEW V_HOME_MONITOR_TASK_COUNTS AS
(
select t.type, t.start_time as start_date, count(1) as counts, sys_guid() as union_id
from (
         SELECT k_trans_record.record_trans_id    AS id,
                1                                 AS type,
                TO_CHAR(start_time, ''YYYY-MM-DD'') AS start_time
                --to_date(start_time, ''yyyy-MM-dd HH24:mm:ss'')
         FROM k_trans_record
         union all
         SELECT k_job_record.record_job_id        AS id,
                2                                 AS type,
                TO_CHAR(start_time, ''YYYY-MM-DD'') AS start_time
         FROM k_job_record
     ) t
group by t.type, t.start_time
    )
;

-- ----------------------------
-- 首页任务执行情况统计视图
-- ----------------------------
create or replace view v_data_collect_counts as
(select d.day, d.num, c.category_name,d.category_id
  from (select to_char(time, ''YYYY-MM-dd'') as day,
               category_id，sum(w) as num
          from k_log
          where type=2
         group by to_char(time, ''YYYY-MM-dd''), category_id
         order by to_char(time, ''YYYY-MM-dd'') desc) d
  left join k_category c
    on d.category_id = c.id
);
